package com.atguigu.gmall.gmallsuger.mapper;

import com.atguigu.gmall.gmallsuger.bean.Product;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.math.BigDecimal;
import java.util.List;

public interface ProductMapper {
    
    // 查询指定天总的销售额
    
    @Select("SELECT sum(order_amount)\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}")
    BigDecimal gmv(@Param("date") int date);  // 20210901
    
    @Select("SELECT\n" +
        "    tm_name name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY tm_name\n" +
        "ORDER BY order_amount DESC\n" +
        "LIMIT #{limit}\n")
    List<Product> gmvByTm(@Param("date") int date, @Param("limit") int limit);
    
    @Select("SELECT\n" +
        "    category3_name AS name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY category3_name\n" +
        "ORDER BY order_amount DESC\n" +
        "LIMIT #{limit}\n")
    List<Product> gmvByC3(@Param("date") int date, @Param("limit") int limit);
    
    @Select("SELECT\n" +
        "    spu_name AS name,\n" +
        "    sum(order_amount) AS order_amount\n" +
        "FROM product_stats_2021\n" +
        "WHERE toYYYYMMDD(stt) = #{date}\n" +
        "GROUP BY spu_name\n" +
        "ORDER BY order_amount DESC\n" +
        "LIMIT #{limit}\n")
    List<Product> gmvBySpu(@Param("date") int date, @Param("limit") int limit);
}
